package CelestialDataAccess;

//import com.microsoft.sqlserver.jdbc.*;
import com.sun.rowset.*;   //provides cached row set implementation
import javax.sql.rowset.*; //provides cached row set interface
import java.sql.*;         //provides connection object and sql exception

public class DataAccessBase {

    static protected String strCnn;  //shared connection string
    static protected Connection cnn; //shared connection object

    /*************************************************************************/
    public void setConnectionString(String cnn_string){ strCnn = cnn_string; }
    public String getConnectionString(){ return strCnn; }
    /*************************************************************************/
   
    public static void open() throws SQLException{

        try{
            if(cnn != null && !cnn.isClosed()) return;

            //Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            //System.out.println("Driver loaded");

            //cnn = DriverManager.getConnection("jdbc:odbc:CelestialDB");
            //System.out.println("Database connected");

            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            System.out.println("Driver loaded");

            cnn = DriverManager.getConnection("jdbc:sqlserver://localhost\\SQLEXPRESS;Database=CelestialDB;integratedSecurity=true");
            System.out.println("Database connected");
           }
        catch(ClassNotFoundException ex){
            System.out.println(ex.toString());
        }
        catch(SQLException ex){
            throw new java.sql.SQLException(ex.getMessage());
        }
    }
    /*************************************************************************/
    public static void close() throws SQLException{
        cnn.close();
    }
    /*************************************************************************/
    public static boolean isOpened(){
        try{
            return !cnn.isClosed();
        } catch (SQLException ex) {
            return false;
        }
    }
    /*************************************************************************/
    static private void testGetData() throws SQLException {

        CachedRowSet rs = new CachedRowSetImpl();
        rs.setUrl("jdbc:sqlserver://localhost\\SQLEXPRESS;Database=CelestialDB;integratedSecurity=true;");
        rs.setCommand("SELECT * FROM tblEmployees");
        rs.execute();

        while(rs.next()){
           // System.out.println(rs.getRow());
            System.out.println(rs.getString("EmployeeID") + "\t" + rs.getString("FirstName") + "\t"+
                    rs.getString("LastName") +"\t" + rs.getString("Username"));
        }
    }
    /*************************************************************************/
    public static void main(String[] args){

        try{
            DataAccessBase.open();
            DataAccessBase.testGetData();

            DataAccessBase.close();
        }
        catch(SQLException ex){
            System.out.println(ex.getMessage() + ex.toString());
        }
    }

}
